﻿using System;
using System.Data;
using System.Drawing;
using System.Windows.Forms;
using Microsoft.Practices.EnterpriseLibrary.Data;
using Microsoft.Practices.EnterpriseLibrary.Data.Oracle;
using WeifenLuo.WinFormsUI.Docking;

namespace GgCarControl
{
    public partial class DISPATCH_ST_STATUS : DockContent
    {
        private class Item
        {
            public string Name;
            public int Value;
            public Item(string name, int value)
            {
                Name = name; Value = value;
            }
            public override string ToString()
            {
                return Name;
            }
        }

        delegate void CheckLabelRereshHandler(DataTable check_cars);
        event CheckLabelRereshHandler CheckRefresh;
        
        public DISPATCH_ST_STATUS()
        {
            InitializeComponent();
            //initiate the rzfs ComboBox and add items for it
            this.comboBox1.Items.Add(new Item("全部", 99));
            this.comboBox1.Items.Add(new Item("已登记", 0));
            this.comboBox1.Items.Add(new Item("已发指令", 1));
            this.comboBox1.Items.Add(new Item("已发卡", 2));
            this.comboBox1.Items.Add(new Item("已进门", 3));
            this.comboBox1.Items.Add(new Item("已理货", 4));
            this.comboBox1.Items.Add(new Item("已出门", 5));
            this.comboBox1.SelectedIndex = 0;
        }

        public static Database db = new OracleDatabase("Data Source=DG;User ID=dgrfid;Password=dgrfid;Unicode=True;Load Balance Timeout=1000");
        DataTable dt;

        System.Timers.Timer t = new System.Timers.Timer(60000);//实例化Timer类，设置间隔时间为60秒；
        private void STATUS_ST_Load(object sender, EventArgs e)
        {
            load_data();

            CheckRefresh += new CheckLabelRereshHandler(fresh_check);
            t.Elapsed += new System.Timers.ElapsedEventHandler(theout);//到达时间的时候执行事件；
            t.AutoReset = true;//设置是执行一次（false）还是一直执行(true)；
            t.Enabled = true;//是否执行System.Timers.Timer.Elapsed事件； 
            BD();

            this.cbbOrder1.DataSource = OrderColumnDataSource();
            this.cbbOrder1.DisplayMember = "displayExpr";
            this.cbbOrder1.ValueMember = "valueExpr";
            this.cbbOrder1.SelectedValue = "";
            this.cbbOrder2.DataSource = OrderColumnDataSource();
            this.cbbOrder2.DisplayMember = "displayExpr";
            this.cbbOrder2.ValueMember = "valueExpr";
            this.cbbOrder2.SelectedValue = "";
        }

        void fresh_check(DataTable dt)
        {
            //string new_number = dt.Rows[0][0].ToString();
            this.label_cars.Text = dt.Rows[0][0].ToString();
        }

        public void theout(object source, System.Timers.ElapsedEventArgs e)
        {
            string sql = "select count(*) as login_check from TRUCK_CARGO_LOGIN where coalesce(CANCEL_FLAG,'0')<>'1' and TRUCKSTATUS='0' and IOYARDID='0'";
            DataSet ds = db.ExecuteDataSet(db.GetSqlStringCommand(sql.ToString()));
            if (this.IsHandleCreated)
            {
                this.label_cars.BeginInvoke(CheckRefresh, ds.Tables[0]);
            }
        }

        private string GetBaseQuerySQL(string date_value_end, string date_value_begin)
        {
            string sql = "select login.logintime as 登记时间,login.truckno as  车号,"+
                "DECODE(CANCEL_FLAG,1,'作废',DECODE(to_number(login.TRUCKSTATUS),0,'登记',1,'发指令',2,DECODE(v.cs,2,'二次过磅',1,'一次过磅','放车'),3,'进门',4,'理货',5,'出门')) as 状态,"+
                "login.SHIPPER_NAME as  货主代理,login.SHIPNAME as 船名,login.BILL_NO as 提单号,login.cargoname as 货名,login.yard as 地点,login.ZYJX as 作业机械,login.stackname as 垛名,"+
                "login.DRIVERPHONE as 司机电话,login.STANDARDS as 规格,login.MATERIAL as 材质,login.GETOWNER as 收货人,login.CARDTIME as 放车时间," +
                "(CASE WHEN login.TRUCKSTATUS=2 THEN to_char(ROUND(TO_NUMBER(sysdate - login.CARDTIME) * 24,1),'fm9999999990.0')||'小时' ELSE '' END) as 已放行时间,login.DOORINTIME AS 进门时间," +
                "(CASE WHEN login.TRUCKSTATUS=3 OR login.TRUCKSTATUS=4 THEN to_char(ROUND(TO_NUMBER(sysdate - login.DOORINTIME) * 24,1),'fm9999999990.0')||'小时' ELSE '' END) as 在港时间," +
                "login.CARDADD as 登记地点,PACKAGE as 包装,login.REMARK as 备注,login.IOYARDID,login.LXH as 流向号,login.WEIGHT as 重量,login.PIECES as 件数," +
                "(select sum(weight) from cargo_thjh where tdh = login.lxh) as 流向重量, (select sum(pieces) from cargo_thjh where tdh = login.lxh) as 流向件数,v.pz as 一次过磅重量,v.mz as 二次过磅重量," +
                "DECODE(LYBZ, '1', '物流网导入', '停车场登记') as 数据来源,login.BAR_CODE as 条码号,decode(login.JFBZ,'0','未收','1','已收','2','不收') as 计费标志,login.DJ as 单价,login.JE as 金额,"+
                "case when coalesce(login.CANCEL_FLAG,'0')='0' and login.truckstatus=2 and ((coalesce(v.CS,0)=0 and (SYSDATE-login.cardtime)*24>24) or (coalesce(v.CS,0)=1 and (SYSDATE-v.PZRQ)*24>24)) then 1 else 0 end as IsTimeOut " +
                "from TRUCK_CARGO_LOGIN login left join v_jlb_tcc_max v on login.bar_code = v.bar_code " +
                "where TO_NUMBER(login.TRUCKSTATUS)<>9 and login.ioyardid=0 and (coalesce(login.JFBZ,'0')='1' or coalesce(login.JFBZ,'0')='2') "+
                "and trunc(login.logintime)<=to_date('" + date_value_end + "','yyyy-mm-dd') and trunc(login.logintime)>=to_date('" + date_value_begin + "','yyyy-mm-dd') ";

            return sql;
        }

        private void load_data()
        {
            //Begin time value
            string date_time_begin = this.dateTimePicker1.Value.ToString();
            int ind = date_time_begin.IndexOf(" ");
            string date_value_begin = date_time_begin.Substring(0, ind);
            //End time value
            string date_time_end = this.dateTimePicker2.Value.ToString();
            int ind2 = date_time_end.IndexOf(" ");
            string date_value_end = date_time_end.Substring(0, ind2);

            string strcm = this.cb_cm.Text.ToString();
            string strhm =  this.cb_hz.Text.ToString();
            string strdm =  this.cb_dm.Text.ToString();
            string strdjdd =  this.cb_dl.Text.ToString();
            string strhzdl =  this.cb_hzdl.Text.ToString();

            //string sql = "select * from TRUCK_LOGIN where TRUCKSTATUS=0 and ioyardid=1";
            string sql = GetBaseQuerySQL(date_value_end, date_value_begin);
            
            string loginSL_SQL = "select sum(1) as sl from TRUCK_CARGO_LOGIN where coalesce(CANCEL_FLAG,'0')<>'1' and TRUCKSTATUS<>'9' and ioyardid=0 and " +
                "trunc(logintime)<=to_date('" + date_value_end + "','yyyy-mm-dd') and trunc(logintime)>=to_date('" + date_value_begin + "','yyyy-mm-dd') ";

            string fcs_SQL = "select sum(1) as sl from TRUCK_CARGO_LOGIN where coalesce(CANCEL_FLAG,'0')<>'1' and (truckstatus='2' or truckstatus='3' or truckstatus='4' or truckstatus='5') and ioyardid=0 and " +
                "trunc(COMMANDTIME)<=to_date('" + date_value_end + "','yyyy-mm-dd') and trunc(COMMANDTIME)>=to_date('" + date_value_begin + "','yyyy-mm-dd') ";

            string timeout_SQL = "select count(1) from TRUCK_CARGO_LOGIN login left join v_jlb_tcc_max v on login.bar_code = v.bar_code where coalesce(login.cancel_flag,'0')='0' and login.ioyardid=0 " +
                "and (coalesce(login.JFBZ,'0')='1' or coalesce(login.JFBZ,'0')='2') and login.truckstatus=2 and ((coalesce(v.CS,0)=0 and (SYSDATE-login.cardtime)*24>24) or (coalesce(v.CS,0)=1 and (SYSDATE-v.PZRQ)*24>24)) " +
                "and trunc(login.logintime)<=to_date('" + date_value_end + "','yyyy-mm-dd') and trunc(login.logintime)>=to_date('" + date_value_begin + "','yyyy-mm-dd') "; 
            
            if (strcm.Trim()!="")
            {
                sql += " AND  login.SHIPNAME LIKE '%" + strcm + "%' ";
                loginSL_SQL += " AND SHIPNAME LIKE '%" + strcm + "%' ";
                fcs_SQL += " AND SHIPNAME LIKE '%" + strcm + "%' ";
                timeout_SQL += " AND  login.SHIPNAME LIKE '%" + strcm + "%' ";
            }
            if (strhm.Trim() != "")
            {
                sql += " AND  login.cargoname LIKE '%" + strhm + "%' ";
                loginSL_SQL += " AND cargoname LIKE '%" + strhm + "%' ";
                fcs_SQL += " AND cargoname LIKE '%" + strhm + "%' ";
                timeout_SQL += " AND  login.cargoname LIKE '%" + strhm + "%' ";
            }
            if (strdm.Trim() != "")
            {
                sql += " AND  login.stackname LIKE '%" + strdm + "%' ";
                loginSL_SQL += " AND stackname LIKE '%" + strdm + "%' ";
                fcs_SQL += " AND stackname LIKE '%" + strdm + "%' ";
                timeout_SQL += " AND  login.stackname LIKE '%" + strdm + "%' ";
            }
            if (strdjdd.Trim() != "")
            {
                sql += " AND  login.CARDADD LIKE '%" + strdjdd + "%' ";
                loginSL_SQL += " AND CARDADD LIKE '%" + strdjdd + "%' ";
                fcs_SQL += " AND CARDADD LIKE '%" + strdjdd + "%' ";
                timeout_SQL += " AND  login.CARDADD LIKE '%" + strdjdd + "%' ";
            }
            if (strhzdl.Trim() != "")
            {
                sql += " AND  (login.SHIPPER_NAME LIKE '%" + strhzdl + "%' OR login.GETOWNER LIKE '%" + strhzdl + "%') ";
                loginSL_SQL += " AND  (SHIPPER_NAME LIKE '%" + strhzdl + "%' OR GETOWNER LIKE '%" + strhzdl + "%') ";
                fcs_SQL += " AND  (SHIPPER_NAME LIKE '%" + strhzdl + "%' OR GETOWNER LIKE '%" + strhzdl + "%') ";
                timeout_SQL += " AND  (login.SHIPPER_NAME LIKE '%" + strhzdl + "%' OR login.GETOWNER LIKE '%" + strhzdl + "%') ";
            }

            sql += GetOrderByString();
            //loginSL_SQL += "group by cargoname,logintime";
            //fcs_SQL += "group by cargoname,logintime";

            dt = db.ExecuteDataSet(db.GetSqlStringCommand(sql.ToString())).Tables[0];

            this.dataGridView1.AutoGenerateColumns = true;
            this.dataGridView1.AllowUserToAddRows = false;
            this.dataGridView1.DataSource = dt;

            //设置某些字段不可见
            this.dataGridView1.Columns["IOYARDID"].Visible = false;
            this.dataGridView1.Columns["IsTimeOut"].Visible = false;
            //this.dataGridView1.Columns["TRUCKSTATUS"].Visible = false;
            //更新登记为发指令数量
            string sql2 = "select count(*) as login_check from TRUCK_CARGO_LOGIN where coalesce(CANCEL_FLAG,'0')<>'1' and TRUCKSTATUS='0' and IOYARDID='0'";
            DataSet ds = db.ExecuteDataSet(db.GetSqlStringCommand(sql2.ToString()));
            this.label_cars.Text = ds.Tables[0].Rows[0][0].ToString();

            //更新登记数和放车数
            //loginSL_SQL = "select sum(case when sl!=0 then 1 else 0 end) from (" + loginSL_SQL + ")";
            //fcs_SQL = "select sum(case when sl!=0 then 1 else 0 end) from (" + fcs_SQL + ")";
            this.lblLoginSL.Text = db.ExecuteDataSet(db.GetSqlStringCommand(loginSL_SQL)).Tables[0].Rows[0][0].ToString();
            this.lblFCS.Text = db.ExecuteDataSet(db.GetSqlStringCommand(fcs_SQL)).Tables[0].Rows[0][0].ToString();
            this.lblTimeOut.Text = db.ExecuteDataSet(db.GetSqlStringCommand(timeout_SQL)).Tables[0].Rows[0][0].ToString();
        }

        private void button2_Click(object sender, EventArgs e)
        {
            load_data();
            LocateByCarNo();
        }

        private void LocateByCarNo()
        {
            string strCarNo = this.textBox_carNO.Text.ToUpper().Trim();
            if (!string.IsNullOrEmpty(strCarNo))
            {
                foreach (DataGridViewRow row in dataGridView1.Rows)
                {
                    if (row.Cells["车号"].Value.ToString().Contains(strCarNo))
                    {
                        dataGridView1.CurrentCell = row.Cells[0];
                        row.Selected = true;
                        return;
                    }
                }
            }
        }

        private void dataGridView1_DataBindingComplete(object sender, DataGridViewBindingCompleteEventArgs e)
        {
            foreach (DataGridViewRow DGVRow in this.dataGridView1.Rows)
            {
                if (DGVRow != null)
                {
                    if (DGVRow.Cells["IsTimeOut"].Value.ToString() == "1")
                    {
                        DGVRow.DefaultCellStyle.BackColor = Color.Red;
                        DGVRow.DefaultCellStyle.SelectionForeColor = Color.Red;
                        continue;
                    }
                    switch (DGVRow.Cells["状态"].Value as string)
                    {
                        case "发指令":
                            DGVRow.DefaultCellStyle.BackColor = Color.Green;
                            DGVRow.DefaultCellStyle.SelectionForeColor = Color.DarkGreen;
                            break;
                        case "放车":
                            DGVRow.DefaultCellStyle.BackColor = Color.Fuchsia;
                            DGVRow.DefaultCellStyle.SelectionForeColor = Color.Fuchsia;
                            break;
                        case "进门":
                            DGVRow.DefaultCellStyle.BackColor = Color.Orange;
                            DGVRow.DefaultCellStyle.SelectionForeColor = Color.Orange;
                            break;
                        case "理货":
                            DGVRow.DefaultCellStyle.BackColor = Color.LightSkyBlue;
                            DGVRow.DefaultCellStyle.SelectionForeColor = Color.LightSkyBlue;
                            break;
                        case "出门":
                            DGVRow.DefaultCellStyle.BackColor = Color.Yellow;
                            DGVRow.DefaultCellStyle.SelectionForeColor = Color.Yellow;
                            break;
                        case "一次过磅":
                            DGVRow.DefaultCellStyle.BackColor = Color.Pink;
                            DGVRow.DefaultCellStyle.SelectionForeColor = Color.Pink;
                            break;
                        case "二次过磅":
                            DGVRow.DefaultCellStyle.BackColor = Color.Salmon;
                            DGVRow.DefaultCellStyle.SelectionForeColor = Color.Salmon;
                            break;
                        case "作废":
                            DGVRow.DefaultCellStyle.BackColor = Color.Gray;
                            DGVRow.DefaultCellStyle.SelectionForeColor = Color.Gray;
                            break;
                    }
                }
            }
        }

        private void comboBox1_SelectedIndexChanged(object sender, EventArgs e)
        {
            //Begin time value
            string date_time_begin = this.dateTimePicker1.Value.ToString();
            int ind = date_time_begin.IndexOf(" ");
            string date_value_begin = date_time_begin.Substring(0, ind);
            //End time value
            string date_time_end = this.dateTimePicker2.Value.ToString();
            int ind2 = date_time_end.IndexOf(" ");
            string date_value_end = date_time_end.Substring(0, ind2);

            string str = this.comboBox1.Text.ToString();
            Item selected_item = (Item)this.comboBox1.SelectedItem;
            string selected_value = selected_item.Value.ToString();

            string sql = GetBaseQuerySQL(date_value_end, date_value_begin);
            if (str != "全部")
            {
                sql += " and login.TRUCKSTATUS=" + selected_value;
            }
            sql += GetOrderByString();

            dt = db.ExecuteDataSet(db.GetSqlStringCommand(sql.ToString())).Tables[0];

            this.dataGridView1.AutoGenerateColumns = true;
            this.dataGridView1.AllowUserToAddRows = false;
            this.dataGridView1.DataSource = dt;

            //设置某些字段不可见
            this.dataGridView1.Columns["IOYARDID"].Visible = false;
            this.dataGridView1.Columns["IsTimeOut"].Visible = false;
            //this.dataGridView1.Columns["TRUCKSTATUS"].Visible = false;
        }

        //发送进车指令
        private void SendCommandToolStripMenuItem_Click(object sender, EventArgs e)
        {
            //MessageBox.Show(this.dataGridView1.SelectedRows.Count.ToString());
            //string car_no = this.dataGridView1.SelectedRows[0].Cells["车号"].Value.ToString();
            //string login_time = this.dataGridView1.SelectedRows[0].Cells["登记时间"].Value.ToString();

            //String update_sql = "update TRUCK_CARGO_LOGIN set TRUCKSTATUS=1,COMMANDTIME=sysdate where logintime=to_date('" + login_time + "','yyyy-mm-dd hh24:mi:ss') and TRUCKNO='" + car_no + "'";
            //db.ExecuteNonQuery(db.GetSqlStringCommand(update_sql.ToString()));
            if (Session.QX == "0")
            {
                OrderSendCommand();
            }
            else
            {
                for (int i = 0; i < this.dataGridView1.SelectedRows.Count; i++)
                {
                    string status = this.dataGridView1.SelectedRows[i].Cells["状态"].Value.ToString();
                    if (status == "登记")
                    {
                        string car_no = this.dataGridView1.SelectedRows[i].Cells["车号"].Value.ToString();
                        string login_time = this.dataGridView1.SelectedRows[i].Cells["登记时间"].Value.ToString();
                        String update_sql = "update TRUCK_CARGO_LOGIN set TRUCKSTATUS=1,COMMANDTIME=sysdate where logintime=to_date('" + login_time + "','yyyy-mm-dd hh24:mi:ss') and TRUCKNO='" + car_no + "' and TRUCKSTATUS<>'9'";
                        db.ExecuteNonQuery(db.GetSqlStringCommand(update_sql.ToString()));
                        string sID = create_id();
                        string insert_sql = "insert into TRUCK_COMMAND_LOG(ID,TRUCKNO,YHBH,XM,XGSJ,XGNR,IOYARDID,SXFC,MODULE,SEND) "+
                            "values(" + sID + ",'" + car_no + "','"+Session.YHBH+"','"+Session.XM+"',sysdate,'理货中心发送进车指令','0','0','市提车辆状态','1')";
                        db.ExecuteNonQuery(db.GetSqlStringCommand(insert_sql));
                    }
                }
            }
            load_data();
        }

        //取消进车指令
        private void CancelCommandToolStripMenuItem_Click(object sender, EventArgs e)
        {
            //string car_no = this.dataGridView1.SelectedRows[0].Cells["车号"].Value.ToString();
            ////string status = this.dataGridView1.SelectedRows[0].Cells["TRUCKSTATUS"].Value.ToString();
            //string login_time = this.dataGridView1.SelectedRows[0].Cells["登记时间"].Value.ToString();

            //String update_sql = "update TRUCK_CARGO_LOGIN set TRUCKSTATUS=0,COMMANDTIME=sysdate where logintime=to_date('" + login_time + "','yyyy-mm-dd hh24:mi:ss') and TRUCKNO='" + car_no + "'";
            //db.ExecuteNonQuery(db.GetSqlStringCommand(update_sql.ToString()));

            for (int i = 0; i < this.dataGridView1.SelectedRows.Count; i++)
            {
                string status = this.dataGridView1.SelectedRows[i].Cells["状态"].Value.ToString();
                if (status == "发指令")
                {
                    string car_no = this.dataGridView1.SelectedRows[i].Cells["车号"].Value.ToString();
                    string login_time = this.dataGridView1.SelectedRows[i].Cells["登记时间"].Value.ToString();
                    String update_sql = "update TRUCK_CARGO_LOGIN set TRUCKSTATUS=0,COMMANDTIME=sysdate where logintime=to_date('" + login_time + "','yyyy-mm-dd hh24:mi:ss') and TRUCKNO='" + car_no + "' and TRUCKSTATUS<>'9'";
                    db.ExecuteNonQuery(db.GetSqlStringCommand(update_sql.ToString()));
                    string sID = create_id();
                    string insert_sql = "insert into TRUCK_COMMAND_LOG(ID,TRUCKNO,YHBH,XM,XGSJ,XGNR,IOYARDID,SXFC,MODULE,SEND) " +
                        "values(" + sID + ",'" + car_no + "','" + Session.YHBH + "','" + Session.XM + "',sysdate,'理货中心取消进车指令','0','0','市提车辆状态','0')";
                    db.ExecuteNonQuery(db.GetSqlStringCommand(insert_sql));
                }
            }
            load_data();
        }

        /// <summary>
        /// 顺序发送放车指令
        /// </summary>
        private void OrderSendCommand()
        {
            DataTable sortTable = GetSelectedRowsSortTable();
            if (sortTable.Rows.Count > 0)
            {
                //发送放车指令
                foreach (DataRow row in sortTable.Rows)
                {
                    string stackname = row["stackName"].ToString();
                    string sWhere = string.IsNullOrEmpty(stackname) ? "stackname is null" : "stackname='" + stackname + "'";

                    string pack = row["package"].ToString();
                    sWhere += string.IsNullOrEmpty(pack) ? " and package is null" : " and package='" + pack + "'";

                    string login_time = row["loginTime"].ToString();
                    string sql = "select id from truck_cargo_login where TRUCKSTATUS='0' and ioyardid=0 and " + sWhere + " and logintime<to_date('" + login_time + "','yyyy-mm-dd hh24:mi:ss')";
                    DataTable dtResult = db.ExecuteDataSet(db.GetSqlStringCommand(sql)).Tables[0];
                    if (dtResult != null && dtResult.Rows.Count > 0)
                    {
                        MessageBox.Show("请按照垛名、包装形式、登记时间顺序放车！");
                        return;
                    }
                    string car_no = row["truckNo"].ToString();
                    String update_sql = "update TRUCK_CARGO_LOGIN set TRUCKSTATUS=1,COMMANDTIME=sysdate where logintime=to_date('" + login_time + "','yyyy-mm-dd hh24:mi:ss') and TRUCKNO='" + car_no + "' and TRUCKSTATUS<>'9'";
                    db.ExecuteNonQuery(db.GetSqlStringCommand(update_sql.ToString()));

                    string sID = create_id();
                    string insert_sql = "insert into TRUCK_COMMAND_LOG(ID,TRUCKNO,YHBH,XM,XGSJ,XGNR,IOYARDID,SXFC,MODULE,SEND) " +
                        "values(" + sID + ",'" + car_no + "','" + Session.YHBH + "','" + Session.XM + "',sysdate,'理货中心发送进车指令','0','1','市提车辆状态','1')";
                    db.ExecuteNonQuery(db.GetSqlStringCommand(insert_sql));
                }
            }
        }

        private string create_id()
        {
            string sequence = "";
            string sqlStr = "select SEQ_TRUCK.nextval from dual";
            DataSet ds1 = db.ExecuteDataSet(db.GetSqlStringCommand(sqlStr));
            if (ds1.Tables[0].Rows.Count > 0)
            {
                sequence = ds1.Tables[0].Rows[0][0].ToString();
            }
            string year_str = DateTime.Now.Year.ToString();
            string month_str = DateTime.Now.Month.ToString();
            string day_str = DateTime.Now.Day.ToString();
            string id = year_str + month_str + day_str + sequence;
            return id;
        }

        private DataTable GetSelectedRowsSortTable()
        {
            DataTable sortTable = new DataTable();
            sortTable.Columns.Add("truckNo", typeof(string));
            sortTable.Columns.Add("stackName", typeof(string));
            sortTable.Columns.Add("package", typeof(string));
            sortTable.Columns.Add("loginTime", typeof(string));

            for (int i = 0; i < this.dataGridView1.SelectedRows.Count; i++)
            {
                DataGridViewRow row = this.dataGridView1.SelectedRows[i];
                string status = row.Cells["状态"].Value.ToString();
                if (status == "登记")
                {
                    string car_no = row.Cells["车号"].Value.ToString();
                    string stackname = row.Cells["垛名"].Value.ToString();
                    string pack = row.Cells["包装"].Value.ToString();
                    string login_time = row.Cells["登记时间"].Value.ToString();
                    sortTable.Rows.Add(car_no, stackname, pack, login_time);
                }
            }
            if (sortTable.Rows.Count > 0)//排序
            {
                sortTable.DefaultView.Sort = "loginTime asc";
                sortTable = sortTable.DefaultView.ToTable();
            }
            return sortTable;
        }

        private void dataGridView1_CellMouseDown(object sender, DataGridViewCellMouseEventArgs e)
        {
            if(e.Button == MouseButtons.Right)//判读是否是鼠标右键
            {
                //this.dataGridView1.ClearSelection();//清除所有的选中的行
                this.dataGridView1.Rows[e.RowIndex].Selected = true;
                //string status = this.dataGridView1.Rows[e.RowIndex].Cells["TRUCKSTATUS"].Value.ToString();
                ////MessageBox.Show(status);
                //if (status == "0")
                //{
                //    this.SendCommandToolStripMenuItem.Visible = true;
                //    this.CancelCommandToolStripMenuItem.Visible = false;
                //}
                //if (status == "1")
                //{
                //    this.SendCommandToolStripMenuItem.Visible = false;
                //    this.CancelCommandToolStripMenuItem.Visible = true;
                //}
                //if (status == "0" || status == "1")
                //{
                //    contextMenuStrip1.Show(MousePosition.X, MousePosition.Y);//contextMenuStrip1为菜单控件
                //}
                this.SendCommandToolStripMenuItem.Visible = true;
                this.CancelCommandToolStripMenuItem.Visible = true;
                contextMenuStrip1.Show(MousePosition.X, MousePosition.Y);//contextMenuStrip1为菜单控件
            }
        }

        private void dataGridView1_CellEndEdit(object sender, DataGridViewCellEventArgs e)
        {
            if (this.dataGridView1.Columns[e.ColumnIndex].Name.ToUpper() == "地点")
            {
                string car_no = (string)dataGridView1.Rows[e.RowIndex].Cells["车号"].Value.ToString();
                string yard = (string)dataGridView1.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString();
                String update_sql = "update TRUCK_CARGO_LOGIN set YARD='" + yard + "' where  TRUCKNO='" + car_no + "'";
                db.ExecuteNonQuery(db.GetSqlStringCommand(update_sql.ToString()));
            }
            if (this.dataGridView1.Columns[e.ColumnIndex].Name.ToUpper() == "作业机械")
            {
                string car_no = (string)dataGridView1.Rows[e.RowIndex].Cells["车号"].Value.ToString();
                string yard = (string)dataGridView1.Rows[e.RowIndex].Cells[e.ColumnIndex].Value.ToString();
                String update_sql = "update TRUCK_CARGO_LOGIN set ZYJX='" + yard + "' where  TRUCKNO='" + car_no + "'";
                db.ExecuteNonQuery(db.GetSqlStringCommand(update_sql.ToString()));
            }
        }

        private void dateTimePicker2_ValueChanged(object sender, EventArgs e)
        {
            BD();
        }

        private void BD()
        {
            //SHIPNAME,cargoname,stackname,CARDADD,SHIPPER_NAME||GETOWNER
            kjbind(cb_cm, "SHIPNAME");
            kjbind(cb_dl, "CARDADD");
            kjbind(cb_dm, "stackname");
            kjbind(cb_hz, "cargoname");
            kjbind(cb_hzdl, "SHIPPER_NAME||GETOWNER");
        }

        private void kjbind(ComboBox _cb,string _lm)
        {
             _cb.Items.Clear();
            //this.cb_dl.Items.Clear();
            //this.cb_dm.Items.Clear();
            //this.cb_hz.Items.Clear();
            //this.cb_hzdl.Items.Clear();

            string strbegin = dateTimePicker1.Value.ToString("yyyy-MM-dd") + " 00:00:00";
            string strend = dateTimePicker2.Value.ToString("yyyy-MM-dd") + "23:59:59";
            string strwhere2 = " WHERE LOGINTIME>=TO_DATE('" + strbegin + "','yyyy-mm-dd hh24:mi:ss') AND LOGINTIME<=TO_DATE('" + strend + "','yyyy-mm-dd hh24:mi:ss') ";

            string strsq = " SELECT DISTINCT " + _lm + " FROM TRUCK_CARGO_LOGIN " + strwhere2;

            DataTable dt = db.ExecuteDataSet(db.GetSqlStringCommand(strsq.ToString())).Tables[0];

            foreach (DataRow dr in dt.Rows)
            {
                if (!dr.IsNull(0))
                    _cb.Items.Add(dr[0].ToString());
            }
        }

        private void dateTimePicker1_ValueChanged(object sender, EventArgs e)
        {
            BD();
        }

        private string GetOrderByString()
        {
            string strOrder = " order by login.CANCEL_FLAG,login.TRUCKSTATUS,coalesce(v.cs,0),login.logintime desc ";

            string orderColumns = "", column = "";
            if (this.cbbOrder1.SelectedValue != null)
            {
                column = this.cbbOrder1.SelectedValue.ToString();
                orderColumns += "login." + column;
                if (this.radioDown1.Checked)
                    orderColumns += " desc";
                if (column == "TRUCKSTATUS")
                {
                    orderColumns += ",coalesce(v.cs,0)";
                    if (this.radioDown1.Checked)
                        orderColumns += " desc";
                }
            }
            if (this.cbbOrder2.SelectedValue != null)
            {
                column = this.cbbOrder2.SelectedValue.ToString();
                orderColumns += ",login." + column;
                if (this.radioDown2.Checked)
                    orderColumns += " desc";
                if (column == "TRUCKSTATUS")
                {
                    orderColumns += ",coalesce(v.cs,0)";
                    if (this.radioDown2.Checked)
                        orderColumns += " desc";
                }
            }
            if (!string.IsNullOrEmpty(orderColumns))
            {
                strOrder = " order by " + orderColumns.TrimStart(',');
            }
            return strOrder;
        }

        private DataTable OrderColumnDataSource()
        {
            DataTable dtOrder = new DataTable();
            dtOrder.Columns.Add("valueExpr", typeof(string));
            dtOrder.Columns.Add("displayExpr", typeof(string));

            dtOrder.Rows.Add("logintime", "登记时间");
            dtOrder.Rows.Add("truckno", "车号");
            dtOrder.Rows.Add("TRUCKSTATUS", "状态");
            dtOrder.Rows.Add("SHIPPER_NAME", "货主代理");
            dtOrder.Rows.Add("SHIPNAME", "船名");
            dtOrder.Rows.Add("BILL_NO", "提单号");
            dtOrder.Rows.Add("cargoname", "货名");
            dtOrder.Rows.Add("yard", "地点");
            dtOrder.Rows.Add("ZYJX", "作业机械");
            dtOrder.Rows.Add("stackname", "垛名");
            dtOrder.Rows.Add("STANDARDS", "规格");
            dtOrder.Rows.Add("MATERIAL", "材质");
            dtOrder.Rows.Add("GETOWNER", "收货人");
            dtOrder.Rows.Add("CARDTIME", "放车时间");
            dtOrder.Rows.Add("DOORINTIME", "进门时间");
            dtOrder.Rows.Add("CARDADD", "登记地点");
            dtOrder.Rows.Add("PACKAGE", "包装");
            dtOrder.Rows.Add("LXH", "流向号");
            dtOrder.Rows.Add("WEIGHT", "重量");
            dtOrder.Rows.Add("PIECES", "件数");
            dtOrder.Rows.Add("LYBZ", "数据来源");
            dtOrder.Rows.Add("BAR_CODE", "条码号");

            return dtOrder;
        }
    }
}
